import redis
import pymysql
from threading import Thread
import queue
import json
import time
import sys
import socket

global sql #sql语句

def login(user,password,num):
    if num==8:
        if rdb1.get(user) == password:
            return True
        else:
            return False
    elif num==9:
        if rdb2.get(user) == password:
            return True
        else:
            return False


#执行SQL查询
def SQLquery(sql):
    conn=pymysql.connect(host = '127.0.0.1',user = 'root',passwd='Hakurei@5tj5!',port= 3306,db='dormitory',charset='utf8')
    cur = conn.cursor() # 生成游标对象
    cur.execute(sql) # 执行SQL语句
    data = cur.fetchall() # 通过fetchall方法获得数据 得到的数据类型是元组   
    cur.close() # 关闭游标
    conn.close() # 关闭连接
    
    return data

#执行SQL修改
def SQLalter(sql):
    conn=pymysql.connect(host = '127.0.0.1',user = 'root',passwd='Hakurei@5tj5!',port= 3306,db='dormitory',charset='utf8')
    cur = conn.cursor() # 生成游标对象
    cur.execute(sql) # 执行SQL语句
    conn.commit()  #提交数据
    cur.close() # 关闭游标
    conn.close() # 关闭连接
    updateRedis()

def updateRedis():
    sql="select * from admin"
    for line in list(SQLquery(sql)):
        rdb1.set(line[0],line[1])
    sql="select * from user"
    for line in list(SQLquery(sql)):
        rdb2.set(line[0],line[1])


#如果列表第一个元素为0，进行登录验证，如果为1，执行SQL语句
def accept(conn,addr):
    while True:
        msg=conn.recv(102400)
        while msg == b'':
            msg=conn.recv(102400)
        mylist=json.loads(msg)
         #   print(mylist)
         #第一个元素为0：登录，1：SQL查询，2：SQL修改
        if mylist[0] == 0:
            #print(0)
            if login(mylist[1],mylist[2],int(mylist[3])) == True:
                conn.send(b'0')
            else:
                conn.send(b'1')
        if mylist[0] == 1:
            #print(1)
            json_string = json.dumps(list(SQLquery(mylist[1])))
            conn.send(bytes(json_string.encode('utf-8')))
        if mylist[0] == 2:
           # print(2)
            SQLalter(mylist[1]) 
    print(addr, ' 断开连接')
    conn.close()


if __name__ == '__main__':
    server = socket.socket()
    server.setsockopt(socket.SOL_SOCKET,socket.SO_REUSEADDR,1)
    server.bind(('127.0.0.1',8888))
    server.listen(10)
    global rdb1
    rdb1 = redis.Redis(host="127.0.0.1", port=6379, db=8, decode_responses=True)  #打开redis数据库 
    sql="select * from admin"
    for line in list(SQLquery(sql)):
        rdb1.set(line[0],line[1])
    rdb2 = redis.Redis(host="127.0.0.1", port=6379, db=9, decode_responses=True)  #打开redis数据库 
    sql="select * from user"
    for line in list(SQLquery(sql)):
        rdb2.set(line[0],line[1])
            
    print("服务器已开启，正在监听用户的请求……")
    while True:
        conn,addr = server.accept()

        print(addr, '已经建立连接')
        Thread(target=accept, args=(conn, addr)).start()

    server.close()
